﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Dynamic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;


namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.dataGridView1.DataBindingComplete += new DataGridViewBindingCompleteEventHandler(dataGridView1_DataBindingComplete);
        }

        private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            //所以行号的背景色
            for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
            {
                if (i % 2 == 0)
                {
                    dataGridView1.Rows[i].DefaultCellStyle.BackColor = Color.AliceBlue;     //偶数行浅蓝色
                }
                else
                {
                    dataGridView1.Rows[i].DefaultCellStyle.BackColor=  Color.FromArgb(224, 224, 224);
                }
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {

            DataTable dt = new DataTable("cart");
           
            DataColumn dc1 = new DataColumn("prizename", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("point", Type.GetType("System.String"));
            DataColumn dc3 = new DataColumn("number", Type.GetType("System.String"));
            DataColumn dc4 = new DataColumn("key", Type.GetType("System.String"));
            DataColumn dc5 = new DataColumn("value", Type.GetType("System.String"));
           
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            dt.Columns.Add(dc4);
            dt.Columns.Add(dc5);
            //以上代码完成了DataTable的构架，但是里面是没有任何数据的
            for (int i = 0; i < 100; i++)
            {
                DataRow dr = dt.NewRow();
                if (i % 2 == 0)
                {
                    dr["prizename"] = "源数据"+(i*2);

                }
                else
                {
                    dr["prizename"] = "源数据" + (i/2);
                }
                
               
                dr["point"] = 10;
                dr["number"] = 1;
                dr["key"] = "A" + (i + 1);
                dr["value"] = "A00" + (i + 1);
                dt.Rows.Add(dr);
            }

            //   var dt1 = CreateDt1();
            //var dt222=   TableRowTurnToColumn(dt1, new DataColumn[] { dt1.Columns[1], dt1.Columns[2] }, new DataColumn[] { dt1.Columns[3] }, dt1.Columns[4]);
            var strListr = new List<string>();
            strListr.Add(dt.Columns[0].ToString());
            strListr.Add(dt.Columns[1].ToString());
            strListr.Add(dt.Columns[2].ToString());          
            var convertTable = DataTableRowtoCon(strListr.ToArray(), dt,  dt.Columns[3].ToString() ,dt.Columns[4].ToString());
                /* TableRowTurnToColumn(dt, new DataColumn[] { dt.Columns[0], dt.Columns[1], dt.Columns[2] }, new DataColumn[] { dt.Columns[3] }, dt.Columns[4]);*/
            this.dataGridView1.DataSource = convertTable;
        }

        /// <summary>
        /// 行转列统计需要转换列的数据
        /// </summary>
        /// <param name="source">ddatable源表 </param>
        /// <param name="groupColumns">需要分租的列</param>
        /// <param name="captionColumns">需要转换的列的名称</param>
        /// <param name="valueColumn">需要转换的列的数值</param>
        /// <returns></returns>
        public static DataTable TableRowTurnToColumn(DataTable source, DataColumn[] groupColumns, DataColumn[] captionColumns, DataColumn valueColumn)
        {
            if (source == null)
                return null;
            DataTable dt = new DataTable();
            if (groupColumns != null)
                foreach (var item in groupColumns)
                {
                    dt.Columns.Add(item.ColumnName, item.DataType);
                }
            foreach (var item in source.AsEnumerable().Select(p => string.Join("_", captionColumns.Select(q => p[q]))).Distinct())
            {
                if (!dt.Columns.Contains(item.ToString()))
                    dt.Columns.Add(item.ToString());
            }
            var groupdata = source.AsEnumerable().GroupBy(p => string.Join("", groupColumns.Select(q => p[q]))).ToList();
            var groupColNames = groupColumns.Select(p => p.ColumnName);
            groupdata.ForEach(x =>
            {
                DataRow newRow = dt.NewRow();
                foreach (DataColumn col in dt.Columns)
                {
                    if (groupColNames.Contains(col.ColumnName))
                    {
                        newRow[col.ColumnName] = x.FirstOrDefault()[col.ColumnName];
                    }
                    else
                    {
                        var data = x.Where(p => string.Join("_", captionColumns.Select(q => p[q])) == col.ColumnName);
                        if (data.Count() > 0)
                        {                            
                            var value = data.Sum(p => Convert.ToDecimal(p[valueColumn]));
                            //data.Sum(p => Convert.ToDecimal(p[valueColumn]));//统计
                            newRow[col.ColumnName] = value;
                        }
                    }
                }
                dt.Rows.Add(newRow);
            });
            return dt;
        }

        #region 行转列
        /// <summary>
        /// 行转列
        /// </summary>
        /// <param name="ColumnName">固定列列名</param>
        /// <param name="dtable">数据源</param>
        /// <param name="Columns02">不固定列</param>
        ///  <param name="Quantity">数据列</param>
        /// <returns></returns>
        public static DataTable DataTableRowtoCon(string[] ColumnNames, DataTable dtable, string Columns02, string Quantity)
        {
            //从DataTable中读取不重复的行，用来构造新DataTable的列
            DataTable distinct_date = dtable.DefaultView.ToTable(true, ColumnNames);

            DataTable newDt = new DataTable();
            foreach (string ColumnName in ColumnNames)
            {
                //创建固定列
                DataColumn col01 = new DataColumn(ColumnName, Type.GetType("System.String"));
                newDt.Columns.Add(col01);
            }
            //循环创建不固定列
            for (int i = 0; i < dtable.Rows.Count; i++)
            {
                if (!newDt.Columns.Contains(dtable.Rows[i][Columns02].ToString()))
                {

                    DataColumn col02 = new DataColumn(dtable.Rows[i][Columns02].ToString(), Type.GetType("System.String"));
                    newDt.Columns.Add(col02);
                }
            }
            //给新表添加数据
            foreach (DataRow dr in distinct_date.Rows)
            {
                DataRow new_dr = newDt.NewRow();
                string dr_Where = string.Empty;//从不重复的数据中提取固定列的数据来组成对比条件
                foreach (string ColumnName in ColumnNames)
                {
                    new_dr[ColumnName] = dr[ColumnName].ToString();//给固定列添加数据
                    dr_Where += dr[ColumnName].ToString();
                }
                foreach (DataRow dr02 in dtable.Rows)
                {
                    string dr02_Where = string.Empty;//从原始数据中提取固定列的数据来组成对比条件
                    foreach (string ColumnName in ColumnNames)
                    {
                        dr02_Where += dr02[ColumnName].ToString();
                    }
                    if (dr_Where.Equals(dr02_Where))
                    {
                        new_dr[dr02[Columns02].ToString()] = dr02[Quantity].ToString();//给非固定列添加数据
                    }
                }
                newDt.Rows.Add(new_dr);
            }
            return newDt;
        }
        #endregion 

        DataTable CreateDt1()
        {
            var dt = new DataTable();
            dt.Columns.Add("id");
            dt.Columns.Add("编号");
            dt.Columns.Add("姓名");
            dt.Columns.Add("类型");
            dt.Columns.Add("值");
            dt.Rows.Add(1, "0001", "张三", "身高", "180");
            dt.Rows.Add(2, "0001", "张三", "体重", "120");
            dt.Rows.Add(3, "0002", "李四", "身高", "170");
            dt.Rows.Add(4, "0002", "李四", "体重", "130");
            dt.Rows.Add(5, "0003", "王五", "身高", "175");
            dt.Rows.Add(6, "0003", "王五", "体重", "135");
            return dt;
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void 导出_Click(object sender, EventArgs e)
        {
            ExportExcels(null,this.dataGridView1);
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <param name="myDGV">控件DataGridView</param>
        private void ExportExcels(string fileName, DataGridView myDGV)
        {
            string saveFileName = "";
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
                                                                                                                                  //写入标题
            for (int i = 0; i < myDGV.ColumnCount; i++)
            {
                worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
            }
            //写入数值
            for (int r = 0; r < myDGV.Rows.Count; r++)
            {
                for (int i = 0; i < myDGV.ColumnCount; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                }
            }
            xlApp.Quit();
            GC.Collect();//强行销毁
            MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}
